package com.sanley.piss.web.excel;

import com.sanley.piss.mapper.DictionaryDataMapper;
import com.sanley.piss.mapper.OrganizationMapper;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormat;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.List;
import java.util.Set;

/**
 * Create By  on 2021/4/12.
 *
 * @author 张凯大天才
 */
@Service
public class ExcelService {
    @Autowired
    private OrganizationMapper organizationMapper;

    @Autowired
    private DictionaryDataMapper dataMapper;

    /**
     * 导出空白导入模板
     *
     * @param response
     * @param title
     */
    public void exportNullFile(HttpServletResponse response, String title) {
        try (HSSFWorkbook workbook = new HSSFWorkbook()) {
            HSSFSheet sheet = workbook.createSheet(title);
            HSSFRow row = sheet.createRow(0);
            //基本信息表
            String[] headers = {"部门", "姓名", "性别", "年龄", "身高", "体重", "血型", "生日类型", "生日日期", "喜好", "电话号码", "微信", "QQ", "邮箱"
                    , "服装型号", "鞋号", "过敏药", "住房地址", "岗位类型", "职务类型", "工号","加入公司时间", "在职情况", "离职时间", "合同类型"};
            //设置单元格格式
            HSSFRow row1 = sheet.createRow(1);
            CellStyle textStyle = workbook.createCellStyle();
            DataFormat format = workbook.createDataFormat();
            textStyle.setDataFormat(format.getFormat("@"));
            row1.createCell(10).setCellStyle(textStyle);
            row1.createCell(11).setCellStyle(textStyle);
            row1.createCell(12).setCellStyle(textStyle);
            row1.createCell(14).setCellStyle(textStyle);
            row1.createCell(20).setCellStyle(textStyle);
            for (short i = 0; i < headers.length; i++) {
                HSSFCell cell = row.createCell(i);
                HSSFRichTextString text = new HSSFRichTextString(headers[i]);
                cell.setCellValue(text);
            }
            Set<String> orgName = organizationMapper.selectOrgIdAndName();
            FilePortUtil.setHSSFValidation(sheet, orgName.toArray(String[]::new), 1, 1, 0, 0);
            String[] sex = {"男", "女"};
            FilePortUtil.setHSSFValidation(sheet, sex, 1, 1, 2, 2);
            List<String> blood = dataMapper.selectDataByType("血型");
            FilePortUtil.setHSSFValidation(sheet, blood.toArray(String[]::new), 1, 1, 6, 6);
            List<String> birthdayType = dataMapper.selectDataByType("生日类型");
            FilePortUtil.setHSSFValidation(sheet, birthdayType.toArray(String[]::new), 1, 1, 7, 7);
            List<String> position = dataMapper.selectDataByType("岗位类型");
            FilePortUtil.setHSSFValidation(sheet, position.toArray(String[]::new), 1, 1, 18, 18);
            List<String> job = dataMapper.selectDataByType("职位类型");
            FilePortUtil.setHSSFValidation(sheet, job.toArray(String[]::new), 1, 1, 19, 19);
            List<String> workingConditions = dataMapper.selectDataByType("在职情况");
            FilePortUtil.setHSSFValidation(sheet, workingConditions.toArray(String[]::new), 1, 1, 22, 22);
            List<String> contractType = dataMapper.selectDataByType("合同类型");
            FilePortUtil.setHSSFValidation(sheet, contractType.toArray(String[]::new), 1, 1, 24, 24);

            //家庭成员表
            HSSFRow row2 = sheet.createRow(2);
            HSSFRow row3 = sheet.createRow(1);
            row3.createCell(3).setCellStyle(textStyle);
            String[] headerFamily = {"姓名","家庭成员姓名", "家庭关系", "联系方式", "生日", "服装型号", "鞋号", "过敏药物", "是否为公司员工"};
            for (short i = 0; i < headerFamily.length; i++) {
                HSSFCell cell = row2.createCell(i);
                HSSFRichTextString text = new HSSFRichTextString(headerFamily[i]);
                cell.setCellValue(text);
            }
            List<String> relation = dataMapper.selectDataByType("家庭关系");
            FilePortUtil.setHSSFValidation(sheet, relation.toArray(String[]::new), 3, 3, 2, 2);
            String[] empcom = {"是", "否"};
            FilePortUtil.setHSSFValidation(sheet, empcom, 3, 3, 8, 8);

            //档案表
            HSSFRow row4 = sheet.createRow(4);
            String[] archives={"姓名","民族","籍贯","户口类型","户口所在地","政治面貌","婚姻情况","身份证号","岗位变动情况","现部门","入职日期","岗位变动日期","转正日期","合同起始日期","合同结束日期","工资卡号"};
            for (short i = 0; i < archives.length; i++) {
                HSSFCell cell = row4.createCell(i);
                HSSFRichTextString text = new HSSFRichTextString(archives[i]);
                cell.setCellValue(text);
            }
            List<String> account = dataMapper.selectDataByType("户口类型");
            FilePortUtil.setHSSFValidation(sheet, account.toArray(String[]::new), 5,5,3, 3);
            Set<String> nowDept = organizationMapper.selectOrgIdAndName();
            FilePortUtil.setHSSFValidation(sheet, nowDept.toArray(String[]::new), 5, 5, 9, 9);

            //工作经历
            HSSFRow row6 = sheet.createRow(6);
            String[] wordExperience={"姓名","公司名称","部门","职位","岗位","入职时间","岗位描述"};
            for (short i = 0; i < wordExperience.length; i++) {
                HSSFCell cell = row6.createCell(i);
                HSSFRichTextString text = new HSSFRichTextString(wordExperience[i]);
                cell.setCellValue(text);
            }

            HSSFRow row5 = sheet.createRow(8);
            String[] educationalBackground={"姓名","学校","专业","入校时间","结业时间","学历","是否取得学位","证书名称","证书类型","证书编号","发证机构","发证日期","到期日期"};
            for (short i = 0; i < educationalBackground.length; i++) {
                HSSFCell cell = row5.createCell(i);
                HSSFRichTextString text = new HSSFRichTextString(educationalBackground[i]);
                cell.setCellValue(text);
            }
            String[] isDegree={"是","否"};
            FilePortUtil.setHSSFValidation(sheet, isDegree, 9,9,6, 6);

            /*application/vnd.ms-excel告诉浏览器要下载的是个excel*/
            response.setContentType("application/vnd.ms-excel;charset=UTF-8");
            /*请求头设置，Content-Disposition为下载标识，attachment标识以附件方式下载*/
            response.addHeader("Content-Disposition", "attachment;filename=" + new String((title).getBytes(), "ISO8859-1") + ".xlsx");
            workbook.write(response.getOutputStream());
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}
